﻿

--提取离职审批列表
CREATE proc [dbo].[proc_Employee_Dimission_GetApplyList]
(
	@startIndex nvarchar(20),
	@endIndex nvarchar(20),
	@docount bit,
	@ename nvarchar(50),
	@companyid nvarchar(800),
	@audit nvarchar(4)
)
as
declare @sql nvarchar (4000)
DECLARE @te NVARCHAR(2000)
SET @te=''
IF(@companyid<>'0')
SET @te=@te+'e.companyid in ('+@companyid+')'
ELSE
SET @te=@te+'e.id<>0 and e.companyid is not null'

if (@ename<>'%')
SET @te=@te+'	and e.ename like ''%'+@ename+'%'''

if(@audit<> '%')
SET @te=@te+'  and (case when ee.audit=1 then 1 when ee.audit=0 and ee.auditperson is null then 0 when ee.audit=0 and ee.auditperson is not null then 2 end) ='+@audit+''


BEGIN				
	set @sql =
		+ ' declare @indextable table(id int identity(1,1),nid int)'
		+ ' insert into @indextable(nid)'
		+ ' select EE.id from employee_Dimission EE inner join Employee E on EE.EId=E.Id'
	    + ' where ee.Proposer is not null and '
		+  @te
		
	--	+ 'and (case when ee.audit=1 then 1' 
	--	+ '		   when ee.audit=0 and ee.auditperson is null then 0'
	--	+ '		   when ee.audit=0 and ee.auditperson is not null then 2 end) like ''%'+@audit+'%'''
		+ ' order By ee.audit,EE.LizhiDate Desc'
		+ ' select' 
		+ ' EE.Id,'
		+ ' (select ENumber from Employee E where EE.EId=E.Id) as ENumber,'
		+ ' (select ETempNumber from Employee E where EE.EId=E.Id) as ETempNumber,'
		+ ' EE.EId,'
		+ ' (select EName from Employee E where EE.EId=E.Id) as EName,'
		+ ' (select Sex from Employee E where EE.EId=E.Id) as Sex,'
		+ ' (select CompanyName from Company where id=(select companyid from employee E where EE.EId=E.Id)) as CompanyName,'
		+ ' (select DName from Department where id=(select departmentid from employee E where EE.EId=E.Id)) as DName,'
		+ ' (select Birthday from Employee E where EE.EId=E.Id) as Birthday,'
		+ ' (select Title from Employee_Education where id=(select Education from employee E where EE.EId=E.Id)) as Education,'
		+ ' (select PostName from Post P where id=(select PostId from employee E where EE.EId=E.Id)) as PostName,'
		+ ' (SELECT COUNT(0) FROM @indextable ) AS coun,'
		+ ' EE.Audit,'
		+ ' EE.AuditPerson,'
		+ ' (select EName from Employee E where EE.AuditPerson=E.UserName) as AuditPersonName,'
		+ ' EE.AuditPerson,'
		+ ' EE.AuditTime,EE.LizhiDate'

		+ ' from employee_Dimission  EE'
		+ '	inner join @indextable t on '
		+ ' EE.id=t.nid	 '
		+ ' where t.id between '+ @startIndex +' and '+ @endIndex 
		+ ' order by t.id'	
	END

EXEC(@sql)
print (@sql)


----提取离职审批列表
--ALTER proc [dbo].[proc_Employee_Dimission_GetApplyList]
--(
--	@startIndex int,
--	@endIndex int,
--	@docount bit,
--	@ename nvarchar(50),
--	@companyid nvarchar(4),
--	@audit nvarchar(4)
--)
--as

--set nocount on
--	if(@docount=1)
--		select *,'EName' as EName,'Sex' as Sex,'CompanyName' as CompanyName,'' AS AuditPersonName,'DName'as DName,'Birthday'as Birthday,'Education' as Education,'PostName' as PostName from employee_Dimission EE inner join Employee E on EE.EId=E.Id
--		where
--			    e.ename like '%'+@ename+'%'
--			and e.companyid like @companyid
--			and (case when ee.audit=1 then '1' 
--				   when ee.audit=0 and ee.auditperson is null then '0'
--				   when ee.audit=0 and ee.auditperson is not null then '1' end) like '%'+@audit+'%'
--			and ee.Proposer is not null
--	else
--	begin
--	declare @indextable table(id int identity(1,1),nid int)
----	set rowcount @endIndex
--	insert into @indextable(nid) select EE.id from employee_Dimission EE inner join Employee E on EE.EId=E.Id  
--	where
--		    e.ename like '%'+@ename+'%'
--		and e.companyid like @companyid
--		and (case when ee.audit=1 then '1' 
--				   when ee.audit=0 and ee.auditperson is null then '0'
--				   when ee.audit=0 and ee.auditperson is not null then '1' end) like '%'+@audit+'%'
--		and ee.Proposer is not null
--	order by EE.id asc
--	select 
--		EE.Id,
--		(select ENumber from Employee E where EE.EId=E.Id) as ENumber,
--		(select ETempNumber from Employee E where EE.EId=E.Id) as ETempNumber,
--		EE.EId,
--		(select EName from Employee E where EE.EId=E.Id) as EName,
--		(select Sex from Employee E where EE.EId=E.Id) as Sex,
--		(select CompanyName from Company where id=(select companyid from employee E where EE.EId=E.Id)) as CompanyName,
--		(select DName from Department where id=(select departmentid from employee E where EE.EId=E.Id)) as DName,
--		(select Birthday from Employee E where EE.EId=E.Id) as Birthday,
--		(select Title from Employee_Education where id=(select Education from employee E where EE.EId=E.Id)) as Education,
--		(select PostName from Post P where id=(select PostId from employee E where EE.EId=E.Id)) as PostName,
--		(SELECT COUNT(0) FROM @indextable ) AS coun,
--		EE.Audit,
--		EE. AuditPerson,
--		(select EName from Employee E where EE.AuditPerson=E.UserName) as AuditPersonName,
--		EE.AuditTime,EE.LizhiDate
--	from employee_Dimission EE
--	inner join @indextable t on 
--	EE.id=t.nid
--	where 
--		t.id between @startIndex and @endIndex 
--	order by t.id
--	end
--set nocount off
RETURN
